﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace skydream
{
    /// <summary>
    /// 数据查询服务
    /// </summary>
    public class SqlHelp
    {

        /// <summary>
        /// 根据字段生成连接
        /// </summary>
        /// <param name="fileDb3"></param>
        /// <returns></returns>
        public static SqlConnection getConn(string str)
        {
            SqlConnection sqlconn = new SqlConnection(str);
            return sqlconn;
        }
        //
        // 摘要:
        //     sql基本查询（根据链接，查询命令）
        //
        // 返回结果:
        //     返回dataSet集合
        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  static DataSet getBasicTable(SqlConnection conn, string sql)
        {
            SqlCommand mycmd = new SqlCommand(sql, conn);
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(mycmd);
            adapter.Fill(ds);
            return ds;
        }
        //
        // 摘要:
        //     sql插入操作（根据链接，插入命令）
        //
        // 返回结果:
        //     返回null
        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        public static void insertCmd(SqlConnection conn, string sql)
        {
            SqlCommand mycmd = new SqlCommand(sql, conn);
            mycmd.CommandText = sql;
            mycmd.ExecuteNonQuery();
        }
        //
        // 摘要:
        //     sql插入操作（根据链接，插入命令）
        //
        // 返回结果:
        //     返回null
        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        public static int insertRCmd(SqlConnection conn, string sql)
        {
            SqlCommand mycmd = new SqlCommand(sql, conn);
            mycmd.CommandText = sql;
            int i=mycmd.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 根据dataSet 得到字段列表
        /// </summary>
        /// <param name="ds_s">dataset</param>
        /// <param name="str_no">不需要的字段列表 id,up_g</param>
        /// <returns></returns>
        public static ArrayList getField(DataSet ds_s, string str_no)
        {
            ArrayList obj = new ArrayList();
            Hashtable ht = new Hashtable();//存放了键值对的类型
            DataColumnCollection dcc = ds_s.Tables[0].Columns;
            string a = "";//独立字段全部取出
            for (int s = 0; s < dcc.Count; s++)
            {
                Type tp = dcc[s].DataType;//字段类型
                bool bl_auto = dcc[s].AutoIncrement;//是否自增长，吧此列不作为修改内容
                if (str_no.Contains(dcc[s].ToString() + ","))//  bl_auto||dcc[s].ToString()=="id")
                {
                    //自增长类型的字段不许要修改和处理。
                }
                else
                {
                    a += dcc[s] + ",";
                    ht.Add(dcc[s].ToString(), tp.Name);
                }
            }
            //扔掉最后一个，号
            if (a.EndsWith(",".ToString()))
            {
                a = a.TrimEnd(",".ToCharArray());//扔掉最后一个字符
            }
            obj.Add(a);
            obj.Add(ht);
            return obj;
        }
        /// <summary>
        /// 根据datatable 得到字段列表
        /// </summary>
        /// <param name="ds_s">dataset</param>
        /// <param name="str_no">不需要的字段列表 id,up_g</param>
        /// <returns></returns>
        public static ArrayList getField(DataTable ds_s, string str_no)
        {
            ArrayList obj = new ArrayList();
            Hashtable ht = new Hashtable();//存放了键值对的类型
            DataColumnCollection dcc = ds_s.Columns;
            string a = "";//独立字段全部取出
            for (int s = 0; s < dcc.Count; s++)
            {
                Type tp = dcc[s].DataType;//字段类型
                bool bl_auto = dcc[s].AutoIncrement;//是否自增长，吧此列不作为修改内容
                if (str_no.Contains(dcc[s].ToString() + ","))//  bl_auto||dcc[s].ToString()=="id")
                {
                    //自增长类型的字段不许要修改和处理。
                }
                else
                {
                    a += dcc[s] + ",";
                    ht.Add(dcc[s].ToString(), tp.Name);
                }
            }
            //扔掉最后一个，号
            if (a.EndsWith(",".ToString()))
            {
                a = a.TrimEnd(",".ToCharArray());//扔掉最后一个字符
            }
            obj.Add(a);
            obj.Add(ht);
            return obj;
        }

        /// <summary>
        /// 根据字段，每行数据获取，执行类型，获得需要的字段
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="dr"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string getFieldSql(ArrayList obj, DataRow dr, string type)
        {
            string[] str_all = obj[0].ToString().Split(new char[] { ',' });//所有字段的依次组合
            Hashtable ht = (Hashtable)obj[1];//字段类型的键值对
            string sql1 = "";
            if (type == "insert")
            {
                //从第0个字段开始拼凑
                for (int i = 0; i < str_all.Length; i++)
                {

                    if (ht[str_all[i]].ToString() == "String" || ht[str_all[i]].ToString() == "DateTime")
                    {
                        sql1 += "'" + dr[str_all[i]].ToString() + "',";
                    }
                    else//不是字符的情况
                    {
                        if (dr[str_all[i]].ToString() == "")//数字为空的情况
                        {
                            sql1 += "NULL,";
                        }
                        else
                        {
                            sql1 += "" + dr[str_all[i]].ToString() + ",";
                        }
                    }
                }
            }
            else
            {
                //从第0个字段开始拼凑
                for (int i = 0; i < str_all.Length; i++)
                {

                    if (ht[str_all[i]].ToString() == "String" || ht[str_all[i]].ToString() == "DateTime")
                    {
                        sql1 += str_all[i] + "='" + dr[str_all[i]].ToString() + "',";
                    }
                    else
                    {
                        if (dr[str_all[i]].ToString() == "")
                        {
                            sql1 += str_all[i] + "=NULL";
                        }
                        else
                        {
                            sql1 += str_all[i] + "=" + dr[str_all[i]].ToString() + ",";
                        }
                    }
                }
            }
            //扔掉最后一个，号
            if (sql1.EndsWith(",".ToString()))
            {
                sql1 = sql1.TrimEnd(",".ToCharArray());//扔掉最后一个字符
            }
            return sql1;
        }

    }
}
